package getFundInfo;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Scanner;

import utils.DateUtils;
import net.sf.json.JSONArray;

public class MonitorFund {

	static String driverName = "org.h2.Driver";
	static String url = "jdbc:h2:C:/Users/ps/Desktop/cache/fundMonitor/dbFile";
	static String dwjzUrl = "http://fund.eastmoney.com/pingzhongdata/";
	static String userName = "sa";
	static String password = "";
	public static Connection conn = null;
	public static String[] codes = {"164402","590002","000928","001409","000534","161610","001195","398001","202002","481001"};
	public static HashMap<String, String> codeMap = new HashMap<String, String>() {
		private static final long serialVersionUID = 1L;
		{
			put("164402", "1");
			put("590002", "0.6,0.61");
			put("000928", "0.6");
			put("001409", "0.6");
			put("000534", "0.6");
			put("161610", "0.6");
			put("001195", "0.6");
			put("398001", "0.6");
			put("202002", "0.6");
			put("481001", "0.6");
		}
	};
	
	static ResultSet rs = null;
	
	public MonitorFund(){
		if(conn == null){
			try {
				System.out.println("---oooooo");
				Class.forName(driverName);
				conn = DriverManager.getConnection(url, userName, password);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public static void SendMail() throws SQLException{
		String yesterday = DateUtils.dateToString(DateUtils.addDays(new Date(), -1), "yyyy-MM-dd");
		double jz = 0;
		for(String c:codes){
			rs = conn.createStatement().executeQuery("select * from net_assert_value where fund_date='"+yesterday+"' and fund_code='"+c+"'");
			while(rs.next()){
				jz = rs.getDouble("net_assert_value");
			}
			for(String s:codeMap.get(c).split(",")){
				System.out.println(Double.valueOf(s));
				if(jz < Double.valueOf(s)){
					System.out.println("发邮件!");
					break;
				}
			}
		}
		
	}
	
	public static boolean isCode(String c){
		for(String str:codes){
			if(str.equals(c)) return true;
		}
		return false;
	}
	
	public static void delete(String tableName) throws Exception {
		conn.createStatement().executeUpdate("TRUNCATE TABLE " + tableName);
	}
	
	public static void insert(String fundCode, String fundArray)
			throws Exception {

		rs = conn.createStatement().executeQuery(
				"select count(*) as allCount from NET_ASSERT_VALUE");
		int allCount = 0;
		if (rs.next()) {
			allCount = rs.getInt("allCount");
		}
		System.out.println("总数:allCount:" + allCount);

		JSONArray jsonArray = JSONArray.fromObject(fundArray);
		System.out.println("基金总数:" + jsonArray.size());
		JSONArray tmp = null;
		final int batchSize = 10000;
		System.out.println("开始插入数据");
		String sql = "insert into NET_ASSERT_VALUE(id,fund_code,fund_date,net_assert_value) values(?,'"
				+ fundCode + "',?,?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		for (int i = 0; i < jsonArray.size(); i++) {
			tmp = jsonArray.getJSONArray(i);
			ps.setString(1, String.valueOf(allCount++));
			ps.setString(2, tmp.get(0).toString());
			ps.setDouble(3, Double.valueOf(tmp.get(1).toString()));
			ps.addBatch();
			if (i % batchSize == 0) {
				ps.executeBatch();
			}
		}
		ps.executeBatch();
		ps.close();

	}
	
	public static void createTable() throws Exception {
		Statement s = null;
		try {
			s = conn.createStatement();
			s.executeUpdate("DROP TABLE net_assert_value");
			s.executeUpdate("CREATE TABLE net_assert_value(ID VARCHAR(64) NOT NULL,FUND_DATE CHAR(10) DEFAULT NULL,FUND_CODE CHAR(6) DEFAULT NULL,NET_ASSERT_VALUE DOUBLE DEFAULT NULL,PRIMARY KEY (ID))");
			s.executeUpdate("DROP TABLE fund_info");
			s.executeUpdate("CREATE TABLE fund_info (id char(64),fund_code char(6),fund_name varchar(20),fund_type char(10),fund_short_name char(20))");
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		try {
			s.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	
	public static void createAndInsert() throws Exception {
		Statement s = null;
		DatabaseMetaData meta = conn.getMetaData();
		rs = meta.getTables(null, null, null, new String[] { "TABLE" });
		HashSet<String> set = new HashSet<String>();
		while (rs.next()) {
			set.add(rs.getString("TABLE_NAME"));
		}
		for (String string : set) {
			System.out.println(string);
		}
		try {
			s = conn.createStatement();
			
			if (set.contains("NET_ASSERT_VALUE")) {
				System.out.println("表net_assert_value已经存在!");
				s.executeUpdate("DROP TABLE net_assert_value");
				s.executeUpdate("CREATE TABLE net_assert_value(ID VARCHAR(64) NOT NULL,FUND_DATE CHAR(10) DEFAULT NULL,FUND_CODE CHAR(6) DEFAULT NULL,NET_ASSERT_VALUE DOUBLE DEFAULT NULL,PRIMARY KEY (ID))");
				//s.executeUpdate("CREATE TABLE fund_info (id char(64),fund_code char(6),fund_name varchar(20),fund_type char(10),fund_short_name char(20))");
			} else {
				System.out.println("表net_assert_value不存在,创建新表!");
				s.executeUpdate("CREATE TABLE net_assert_value(ID VARCHAR(64) NOT NULL,FUND_DATE CHAR(10) DEFAULT NULL,FUND_CODE CHAR(6) DEFAULT NULL,NET_ASSERT_VALUE DOUBLE DEFAULT NULL,PRIMARY KEY (ID))");
				s.executeUpdate("CREATE TABLE fund_info (id char(64),fund_code char(6),fund_name varchar(20),fund_type char(10),fund_short_name char(20))");
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		try {
			s.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	
	public static void deleteAndInsertNetAssertValue() throws Exception {
		delete("NET_ASSERT_VALUE");
		for(int i=0;i<codes.length;i++){
			insert(codes[i], GetFundInfo.danWeiJingZhi(dwjzUrl+codes[i]+".js"));
		}
	}

	public static void deleteAndInsertFundInfo() throws Exception {
		delete("fund_info");
		rs = conn.createStatement().executeQuery(
				"select count(*) as allCount from fund_info");
		int allCount = 0;
		if (rs.next()) {
			allCount = rs.getInt("allCount");
		}
		System.out.println("总数:allCount:" + allCount);
		
		
		String funds = GetJson.getResponse("http://fund.eastmoney.com/js/fundcode_search.js");
		funds = funds.substring(funds.indexOf("["),funds.lastIndexOf(";"));
		try {
			
			JSONArray jsonArray = JSONArray.fromObject(funds);
			PreparedStatement ps = null; 
			try {
				JSONArray tmp = null;
				String sql = "insert into fund_info (id, fund_code, fund_short_name, fund_name,  fund_type) values(?, ?, ?,?,?)";
				ps = conn.prepareStatement(sql);
				for(int i=0;i<jsonArray.size();i++){
					tmp = jsonArray.getJSONArray(i);
					ps.setString(1, String.valueOf(allCount++));
					ps.setString(2, tmp.getString(0));
					ps.setString(3, tmp.getString(1));
					ps.setString(4, tmp.getString(2));
					ps.setString(5, tmp.getString(3));
					ps.addBatch();
				}
				ps.executeBatch();
			} catch (Exception e) {
				System.out.println("插入数据失败!");
			}finally{
				ps.close();
			}
			
		} catch (Exception e) {
			System.out.println("JSON转换失败!");
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) throws Exception {
		
		new MonitorFund();
		Scanner in = new Scanner(System.in);
		String inStr;
		//createTable();
		System.out.println("每天更新重点基金信息:"+DateUtils.dateToString(new Date(), "yyyy-MM-dd  hh:mm:ss:"));
		SendMail();
	
		while(true){
			System.out.println("*****************菜单*******************");
			System.out.println("输入exit退出");
			System.out.println("输入1重新更新基金净值信息");
			System.out.println("输入11重新更新基金信息");
			System.out.println("输入2查询基金信息");
			System.out.println("输入代码查询净值历史信息");
			System.out.print("请输入:");
			
			inStr = in.nextLine();
			if("1".equals(inStr)){
				deleteAndInsertNetAssertValue();
			}else if("11".equals(inStr)){
				deleteAndInsertFundInfo();
			}else if("2".equals(inStr)){
				String cs = "";
				for(String s:codes){
					cs+="'"+s+"',";
				}
				cs = cs.substring(0, cs.length()-1);
				rs = conn.createStatement().executeQuery("select * from fund_info where fund_code in("+cs+")");
				while(rs.next()){
					System.out.println(rs.getString("fund_code")+" "+rs.getString("fund_name")+" "+rs.getString("fund_type")+rs.getString("fund_short_name"));
				}
			}else if(isCode(inStr)){
				rs = conn.createStatement().executeQuery("select * from net_assert_value where fund_code ='"+inStr+"'");
				while(rs.next()){
					System.out.println(rs.getString("fund_date")+" "+rs.getString("net_assert_value"));
				}
				rs = conn.createStatement().executeQuery("select * from net_assert_value a where a.fund_code ='"+inStr+"' ORDER BY a.NET_ASSERT_VALUE LIMIT 10");
				System.out.print("最低10条记录->");
				while(rs.next()){
					System.out.print(rs.getString("fund_date")+" ("+rs.getString("net_assert_value")+") ");
				}
				System.out.println();
				System.out.print("最高10条记录->");
				rs = conn.createStatement().executeQuery("select * from net_assert_value a where a.fund_code ='"+inStr+"' ORDER BY a.NET_ASSERT_VALUE desc LIMIT 10");
				while(rs.next()){
					System.out.print(rs.getString("fund_date")+" ("+rs.getString("net_assert_value")+") ");
				}
				System.out.println();
				System.out.println(codeMap.get(inStr));
			}
			
			if("exit".equals(inStr)){
				break;
			}
			
		}
		in.close();
	}
}
